import sys
import os
import json
import datetime
from pathlib import Path
from openpyxl import load_workbook
try:
    import win32com.client
    EXCEL_COM_AVAILABLE = True
except ImportError:
    EXCEL_COM_AVAILABLE = False

# 自定义JSON编码器，处理特殊类型
class NaNEncoder(json.JSONEncoder):
    def default(self, obj):
        # 处理日期时间类型
        if isinstance(obj, (datetime.date, datetime.datetime)):
            return obj.isoformat()
        # 处理浮点数，保留5位小数
        elif isinstance(obj, float):
            return round(obj, 5)
        # 处理其他类型
        try:
            return super().default(obj)
        except TypeError:
            return str(obj)  # 如果无法序列化，转换为字符串

def force_calculate_excel_formulas(file_path):
    """
    使用Excel COM对象打开并保存文件以强制计算公式
    Args:
        file_path: Excel文件路径
    Returns:
        bool: 是否成功执行
    """
    if not EXCEL_COM_AVAILABLE:
        print(f"警告: win32com.client不可用，跳过公式强制计算: {file_path}", file=sys.stderr)
        return False
    
    try:
        # 创建Excel应用程序对象
        excel_app = win32com.client.Dispatch("Excel.Application")
        excel_app.Visible = False  # 不显示Excel界面
        excel_app.DisplayAlerts = False  # 不显示警告对话框
        
        # 打开工作簿
        workbook = excel_app.Workbooks.Open(str(file_path))
        
        # 强制计算所有公式
        excel_app.Calculate()
        
        # 保存文件
        workbook.Save()
        
        # 关闭工作簿
        workbook.Close()
        
        # 退出Excel应用程序
        excel_app.Quit()
        
        print(f"成功强制计算Excel公式: {file_path}", file=sys.stderr)
        return True
        
    except Exception as e:
        print(f"强制计算Excel公式失败: {file_path}, 错误: {str(e)}", file=sys.stderr)
        try:
            # 尝试清理Excel进程
            if 'excel_app' in locals():
                excel_app.Quit()
        except:
            pass
        return False

def format_as_percentage(value, decimal_places=2):
    """
    将数值转换为百分数格式，保留指定小数位数
    Args:
        value: 要转换的数值
        decimal_places: 保留的小数位数，默认2位
    Returns:
        str: 格式化后的百分数字符串
    """
    if value is None:
        return None
    try:
        # 将数值转换为百分数（乘以100）并格式化
        percentage = float(value) * 100
        return f"{percentage:.{decimal_places}f}%"
    except (ValueError, TypeError):
        return value

def get_max_value_row(worksheet, column_index):
    """
    直接从worksheet中获取指定列的最大值所在行
    """
    max_value = None
    max_row_index = None
    row_data = None
    
    # 检查列索引是否有效
    if column_index is None:
        return max_value, max_row_index, row_data, {}
    
    # 获取表头
    headers = [cell.value for cell in worksheet[1]]
    
    # 遍历所有行（跳过表头）
    for i, row in enumerate(worksheet.iter_rows(min_row=2), start=2):
        # 确保列索引在有效范围内
        if column_index < len(row):
            cell = row[column_index]
            cell_value = cell.value
            
            # 跳过错误值，如 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!
            if isinstance(cell_value, str) and cell_value.startswith('#'):
                continue
            
            # 尝试转换为数值
            try:
                if cell_value is not None:
                    numeric_value = None
                    if isinstance(cell_value, (int, float)):
                        numeric_value = float(cell_value)
                    elif isinstance(cell_value, str):
                        # 移除可能的百分比符号或其他非数字字符
                        clean_val = cell_value.replace('%', '').replace(',', '').strip()
                        if clean_val:
                            numeric_value = float(clean_val)
                        else:
                            continue
                    else:
                        continue
                    
                    # 更新最大值
                    if max_value is None or numeric_value > max_value:
                        max_value = numeric_value
                        max_row_index = i
                        # 获取整行数据，并确保浮点数保留5位小数
                        row_data = {}
                        for j, cell in enumerate(row):
                            if j < len(headers):
                                value = cell.value
                                header_name = headers[j]
                                
                                # 对特定字段进行百分数格式化
                                if header_name in ['左右侧压强峰值偏差', '左右侧平均压强偏差']:
                                    value = format_as_percentage(value, 2)
                                # 如果是浮点数，保留5位小数
                                elif isinstance(value, float):
                                    value = round(value, 5)
                                
                                # 处理重复列名的情况
                                if header_name in row_data:
                                    # 如果列名已存在，添加列索引后缀
                                    original_name = header_name
                                    counter = 2
                                    while header_name in row_data:
                                        header_name = f"{original_name}_{counter}"
                                        counter += 1
                                
                                row_data[header_name] = value
            except (ValueError, TypeError):
                continue
    
    return max_value, max_row_index, row_data, {}


def find_column_by_name(headers, column_name):
    """
    在表头中查找指定列名的索引
    """
    for i, header in enumerate(headers):
        if header == column_name:
            return i
    return None


def find_row_by_value(worksheet, column_index, target_value):
    """
    在指定列中查找特定值所在的行
    """
    headers = [cell.value for cell in worksheet[1]]
    target_row = None
    
    for i, row in enumerate(worksheet.iter_rows(min_row=2), start=2):
        if column_index < len(row):
            cell = row[column_index]
            cell_value = cell.value
            
            # 跳过Excel错误值
            if isinstance(cell_value, str) and cell_value.startswith('#'):
                continue
                
            if cell_value is not None:
                # 转换为小写字符串进行比较
                if str(cell_value).strip().lower() == str(target_value).lower():
                    # 获取整行数据，处理可能的Excel错误值，并确保浮点数保留5位小数
                    target_row = {}
                    for j, cell in enumerate(row):
                        if j < len(headers):
                            value = cell.value
                            header_name = headers[j]
                            
                            # 如果是Excel错误值，设为None
                            if isinstance(value, str) and value.startswith('#'):
                                value = None
                            # 对特定字段进行百分数格式化
                            elif header_name in ['左右侧压强峰值偏差', '左右侧平均压强偏差']:
                                value = format_as_percentage(value, 2)
                            # 如果是浮点数，保留5位小数
                            elif isinstance(value, float):
                                value = round(value, 5)
                            target_row[header_name] = value
                    break
    
    return target_row

def extract_after_processed_data(file_path):
    """
    Extract specific data from after_processed Excel files.
    Extracts data from '一小时后' sheet for rows with A column values:
    - '初始max'
    - '一小时后max' 
    - '变化百分比'
    
    Args:
        file_path: Path to the Excel file
    
    Returns:
        List containing file name and extracted data
    """
    try:
        # 加载工作簿
        wb = load_workbook(file_path, data_only=True)
        
        # 检查是否存在'一小时后'工作表
        if '一小时后' not in wb.sheetnames:
            return [Path(file_path).name, {'error': "Sheet '一小时后' not found in the file."}]
        
        ws = wb['一小时后']
        results = [Path(file_path).name]
        
        # 定义需要提取的行标识符和对应的键名
        target_rows = ['初始max', '一小时后max', '变化百分比']
        column_names = [
            'region 4压强峰值/kPa', 'region 4平均压强/kPa',
            'region 5压强峰值/kPa', 'region 5平均压强/kPa', 
            'region 6压强峰值/kPa', 'region 6平均压强/kPa',
            'region 7压强峰值/kPa', 'region 7平均压强/kPa',
            'region 8压强峰值/kPa', 'region 8平均压强/kPa',
            'region 9压强峰值/kPa', 'region 9平均压强/kPa',
            'region 10压强峰值/kPa', 'region 10平均压强/kPa'
        ]
        
        # 遍历每个目标行
        for target_row_name in target_rows:
            # 查找A列中包含目标值的行
            target_row = None
            for row in ws.iter_rows(min_row=1, max_col=15):  # 只检查到O列
                if row[0].value and str(row[0].value).strip() == target_row_name:
                    target_row = row
                    break
            
            if target_row:
                # 提取B列到O列的数据（索引1到14）
                data_dict = {}
                for i, col_name in enumerate(column_names):
                    if i + 1 < len(target_row):  # 确保不超出行的范围
                        cell_value = target_row[i + 1].value  # B列开始，索引为1
                        
                        # 处理可能的Excel错误值
                        if isinstance(cell_value, str) and cell_value.startswith('#'):
                            data_dict[col_name] = None
                        else:
                            # 尝试转换为数值
                            try:
                                if cell_value is not None:
                                    if isinstance(cell_value, (int, float)):
                                        data_dict[col_name] = round(float(cell_value), 5)
                                    else:
                                        # 尝试转换字符串为数字
                                        clean_val = str(cell_value).replace(',', '').strip()
                                        data_dict[col_name] = round(float(clean_val), 5)
                                else:
                                    data_dict[col_name] = None
                            except (ValueError, TypeError):
                                data_dict[col_name] = cell_value
                    else:
                        data_dict[col_name] = None
                
                # 以A列内容为数据块标题
                results.append({target_row_name: data_dict})
            else:
                results.append({'error': f"Row with '{target_row_name}' not found in '一小时后' sheet."})
        
        return results
        
    except Exception as e:
        error_info = {'error': f"Failed to read or process Excel file: {str(e)}"}
        return [Path(file_path).name, error_info]


def extract_data_from_excel(file_path, custom_frame_number=None):
    """
    Extracts specific data from a single Excel file.
    Args:
        file_path: Path to the Excel file
        custom_frame_number: Custom frame number to search in 七区 sheet (optional)
    """
    try:
        file_name = Path(file_path).name
        results = [file_name]

        # 1. '臀部左右' sheet
        try:
            # Load workbook with data_only=True to get formula values
            wb_hip = load_workbook(filename=file_path, data_only=True)
            ws_hip = wb_hip['臀部左右']
            
            # 获取表头行
            header_row = [cell.value for cell in ws_hip[1]]
            
            # 获取表头行信息
            
            # Requirement 1: Find the row with the maximum value in '左右侧压强峰值偏差' column
            col_name_peak = '左右侧压强峰值偏差'
            col_index_peak = find_column_by_name(header_row, col_name_peak)
            
            if col_index_peak is not None:
                # 获取最大值所在行数据
                max_value, max_row_idx, max_row_data, _ = get_max_value_row(ws_hip, col_index_peak)
                # 确保最大值保留5位小数
                if isinstance(max_value, float):
                    max_value = round(max_value, 5)
                
                if max_row_data:
                    results.append({'臀部左右_最大偏差行': max_row_data})
                else:
                    results.append({'error': f"Column '{col_name_peak}' in '臀部左右' sheet contains no valid numeric data. Please check the Excel formulas."})
            else:
                results.append({'error': "Column '左右侧压强峰值偏差' not found in '臀部左右' sheet."})

            # Requirement 2: Find the row with the maximum value in '左右侧平均压强偏差' column
            col_name_avg = '左右侧平均压强偏差'
            col_index_avg = find_column_by_name(header_row, col_name_avg)
            
            if col_index_avg is not None:
                # 获取最大值所在行数据
                max_value_avg, max_row_idx_avg, max_row_data_avg, _ = get_max_value_row(ws_hip, col_index_avg)
                # 确保最大值保留5位小数
                if isinstance(max_value_avg, float):
                    max_value_avg = round(max_value_avg, 5)
                
                if max_row_data_avg:
                    results.append({'臀部左右_最大平均偏差行': max_row_data_avg})
                else:
                    results.append({'error': f"Column '{col_name_avg}' in '臀部左右' sheet contains no valid numeric data. Please check the Excel formulas."})
            else:
                results.append({'error': "Column '左右侧平均压强偏差' not found in '臀部左右' sheet."})

            col_a_name = '帧数'
            col_b_name = 'region 2压强峰值/kPa'
            
            col_a_index = find_column_by_name(header_row, col_a_name)
            col_b_index = find_column_by_name(header_row, col_b_name)
            
            if col_a_index is not None and col_b_index is not None:
                # 查找A列为'max'的行
                max_row = find_row_by_value(ws_hip, col_a_index, 'max')
                
                if max_row:
                    # 获取B列的值
                    b_value = max_row[col_b_name]
                    # 处理可能的Excel错误值
                    if isinstance(b_value, str) and b_value.startswith('#'):
                        b_value = None
                    # 尝试转换为浮点数
                    elif b_value is not None:
                        try:
                            b_value = round(float(b_value), 5)
                        except (ValueError, TypeError):
                            pass
                    results.append({'臀部左右_max行_region2压强峰值': b_value})
                else:
                    results.append({'error': "Row with 'max' in column '帧数' not found in '臀部左右' sheet."})
            else:
                results.append({'error': "Columns '帧数' or 'region2压强峰值/kPa' not found in '臀部左右' sheet."})

        except Exception as e:
            results.append({'error': f"Could not process '臀部左右' sheet: {str(e)}"})

        # 3. '七区' sheet
        try:
            # Load workbook with data_only=True to get formula values
            wb_zones = load_workbook(filename=file_path, data_only=True)
            ws_zones = wb_zones['七区']
            
            # 获取表头行
            header_row_zones = [cell.value for cell in ws_zones[1]]
            
            # 获取七区表头行
            
            # 查找'分区'列的索引
            col_a_name = '帧数'
            col_a_index = find_column_by_name(header_row_zones, col_a_name)
            
            if col_a_index is not None:
                # 查找A列为'max'的行
                max_row = find_row_by_value(ws_zones, col_a_index, 'max')
                
                if max_row:
                    # 获取所有列名，排除第一列（A列）
                    if len(header_row_zones) > 1:
                        # 从第二列（B列）开始到最后一列（如果有15列或更多，则取到O列）
                        cols_to_extract = header_row_zones[1:min(15, len(header_row_zones))]
                        
                        # 检查是否有足够的列
                        if len(cols_to_extract) > 0:
                            # 提取数据并转换为字典
                            data_dict = {}
                            for i, col in enumerate(cols_to_extract):
                                # 使用列索引+1作为键（对应B=1, C=2, ...）
                                col_value = max_row[col]
                                # 处理可能的Excel错误值
                                if isinstance(col_value, str) and col_value.startswith('#'):
                                    data_dict[str(i+1)] = None
                                # 尝试转换为浮点数
                                elif col_value is not None:
                                    try:
                                        data_dict[str(i+1)] = round(float(col_value), 5)
                                    except (ValueError, TypeError):
                                        data_dict[str(i+1)] = col_value
                                else:
                                    data_dict[str(i+1)] = None
                            
                            results.append({'七区_max行_数据': data_dict})
                        else:
                            results.append({'error': "No columns found after column A in '七区' sheet."})
                    else:
                        results.append({'error': "Only one column found in '七区' sheet."})
                else:
                    results.append({'error': "Row with 'max' in column '分区' not found in '七区' sheet."})
            else:
                results.append({'error': "Column '帧数' not found in '七区' sheet."})
            
            # 处理自定义帧数查找（如果提供了custom_frame_number参数）
            if custom_frame_number is not None:
                try:
                    # 查找'帧数'列的索引
                    col_a_index = find_column_by_name(header_row_zones, '帧数')
                    
                    if col_a_index is not None:
                        # 查找A列为custom_frame_number的行
                        custom_frame_row = find_row_by_value(ws_zones, col_a_index, custom_frame_number)
                        
                        if custom_frame_row:
                            # 获取所有列名，排除第一列（A列）
                            if len(header_row_zones) > 1:
                                # 从第二列（B列）开始到最后一列（如果有15列或更多，则取到O列）
                                cols_to_extract = header_row_zones[1:min(15, len(header_row_zones))]
                                
                                # 检查是否有足够的列
                                if len(cols_to_extract) > 0:
                                    # 提取数据并转换为字典
                                    custom_data_dict = {}
                                    # 添加帧数字段作为第一个数据
                                    custom_data_dict['帧数'] = custom_frame_number
                                    for i, col in enumerate(cols_to_extract):
                                        # 使用列索引+1作为键（对应B=1, C=2, ...）
                                        col_value = custom_frame_row[col]
                                        # 处理可能的Excel错误值
                                        if isinstance(col_value, str) and col_value.startswith('#'):
                                            custom_data_dict[str(i+1)] = None
                                        # 尝试转换为浮点数
                                        else:
                                            try:
                                                if col_value is not None:
                                                    if isinstance(col_value, (int, float)):
                                                        custom_data_dict[str(i+1)] = round(float(col_value), 5)
                                                    else:
                                                        # 尝试转换字符串为数字
                                                        clean_val = str(col_value).replace(',', '').strip()
                                                        custom_data_dict[str(i+1)] = round(float(clean_val), 5)
                                                else:
                                                    custom_data_dict[str(i+1)] = None
                                            except (ValueError, TypeError):
                                                custom_data_dict[str(i+1)] = col_value
                                    
                                    results.append({'七区自定义帧': custom_data_dict})
                                else:
                                    results.append({'error': f"No columns found after column A for custom frame {custom_frame_number} in '七区' sheet."})
                            else:
                                results.append({'error': f"Only one column found for custom frame {custom_frame_number} in '七区' sheet."})
                        else:
                            results.append({'error': f"Row with frame number '{custom_frame_number}' not found in '七区' sheet."})
                    else:
                        results.append({'error': "Column '帧数' not found in '七区' sheet for custom frame search."})
                except Exception as e:
                    results.append({'error': f"Could not process custom frame {custom_frame_number} in '七区' sheet: {str(e)}"})

        except Exception as e:
            results.append({'error': f"Could not process '七区' sheet: {str(e)}"})
        
        return results

    except Exception as e:
        error_info = {'error': f"Failed to read or process Excel file: {str(e)}"}
        return [Path(file_path).name, error_info]


def main(folder_path, custom_frame_number=None):
    """
    Main function to process all Excel files in a directory.
    Args:
        folder_path: Path to the folder containing Excel files
        custom_frame_number: Custom frame number to search in 七区 sheet (optional)
    """
    all_files_data = []
    folder = Path(folder_path)
    
    if not folder.is_dir():
        print(json.dumps({"success": False, "error": "Provided path is not a valid directory."}, cls=NaNEncoder))
        return

    excel_files = list(folder.glob('*.xlsx'))
    if not excel_files:
        print(json.dumps({"success": False, "error": "No .xlsx files found in the directory."}, cls=NaNEncoder))
        return

    for file_path in excel_files:
        # 在提取数据之前，先强制计算Excel公式
        print(f"正在处理文件: {file_path.name}", file=sys.stderr)
        force_calculate_excel_formulas(file_path)
        
        # 检查文件名是否以after_processed结尾
        file_stem = file_path.stem  # 获取不带扩展名的文件名
        if file_stem.endswith('after_processed'):
            # 对于after_processed文件，提取特殊数据
            data = extract_after_processed_data(file_path)
        else:
            # 对于普通文件，使用原有逻辑
            data = extract_data_from_excel(file_path, custom_frame_number)
        
        all_files_data.append(data)
    
    # 生成输出文件名（保存在输入文件夹中）
    timestamp = datetime.datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
    output_file = Path(folder_path) / f"base_data_{timestamp}.json"
    
    # 保存JSON文件
    try:
        # 只保留三点需求的数据，去掉其他不必要的数据
        simplified_data = []
        for file_data in all_files_data:
            file_name = file_data[0] if isinstance(file_data, list) and len(file_data) > 0 else "未知文件"
            simplified_file_data = [file_name]
            
            # 提取需求数据
            for item in file_data[1:] if isinstance(file_data, list) else []:
                if isinstance(item, dict):
                    if ('臀部左右_最大偏差行' in item or '臀部左右_最大平均偏差行' in item or 
                        '臀部左右_max行_region2压强峰值' in item or '七区_max行_数据' in item or 
                        '七区自定义帧' in item or 'error' in item or
                        '初始max' in item or '一小时后max' in item or '变化百分比' in item):
                        simplified_file_data.append(item)
            
            simplified_data.append(simplified_file_data)
        
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump({"success": True, "data": simplified_data}, f, ensure_ascii=False, indent=2, cls=NaNEncoder)
        print(json.dumps({"success": True, "data": simplified_data, "output_file": str(output_file)}, cls=NaNEncoder))
    except Exception as e:
        print(json.dumps({"success": False, "error": f"Failed to save JSON file: {str(e)}"}, cls=NaNEncoder))


if __name__ == "__main__":
    if len(sys.argv) > 1:
        folder_path_arg = sys.argv[1]
        # 检查是否提供了自定义帧数
        custom_frame_arg = None
        if len(sys.argv) > 2:
            custom_frame_arg = sys.argv[2]
        main(folder_path_arg, custom_frame_arg)
    else:
        print(json.dumps({"success": False, "error": "No folder path provided."}, cls=NaNEncoder))